﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

public partial class BCDTTongHop : System.Web.UI.Page
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmBCDTTongHop";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double kltx = 0, klban = 0, ttvl = 0,ttvlkhongvat=0, ttvlth = 0, ttban = 0,ttbankhongvat=0, tongchenh = 0, chenhnvkd = 0, chenhcongty = 0, ttchenh = 0, ttnvkd = 0, ttcongty = 0, ttcongtron = 0, ttcvc=0, dthugiavl = 0, dthuvlth = 0, hotrocuoc = 0, tiencabom = 0, tienhang = 0;
    static string ncc = "";
    static System.Data.DataTable dt;
    static System.Data.DataTable dtPrint;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
            }


            CurrentPage = 1;
            Title = "Báo cáo doanh thu tổng hợp | " + Header;
            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dlNam.Visible = false;

            
            LoadKhachHang(dlKhachHang);
            LoadLoaiKH(dlLoaiKH);
            LoadTramTron(dlTramTron);
            LoadCongTrinh(dlCongTrinh);
        }
    }
    void LoadTramTron(DropDownList dlTramTron)
    {
        dlTramTron.Items.Clear();
        dlTramTron.DataSource = ex.GetData("sp_xdanhaphang_ListTramTron");
        dlTramTron.DataBind();
        dlTramTron.Items.Insert(0, new ListItem("--Chọn trạm trộn--", ""));
        dlTramTron.SelectedIndex = 0;
    }
    void LoadLoaiKH(DropDownList dlLoaiKH)
    {
        dlLoaiKH.Items.Clear();
        dlLoaiKH.DataSource = ex.GetData("sp_xdaloaikhachhang_ListDrop");
        dlLoaiKH.DataBind();
        dlLoaiKH.Items.Insert(0, new ListItem("--Chọn LKH--", ""));
        dlLoaiKH.SelectedIndex = 0;
    }
    void LoadKhachHang(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadCongTrinh(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
        dl.SelectedIndex = 0;
    }
    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiBaoCao.SelectedIndex.Equals(0))
            Response.Redirect("BCDTTongHop.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(1))
            Response.Redirect("BCDTMacHopDong.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(2))
            Response.Redirect("BCDTBienSoXe.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(3))
            Response.Redirect("BCDTNVKD.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(4))
            Response.Redirect("BCDTXeBom.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(5))
            Response.Redirect("BCDTGiaVatLieu.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(6))
            Response.Redirect("BCDTKhachHang.aspx");
        else if(dlLoaiBaoCao.SelectedIndex.Equals(7))
            Response.Redirect("BCDTVatLieuTieuHao.aspx");
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        if (f_SuDung.Equals(1))
        {
            CurrentPage = 1;
            btnNext.Enabled = true;
            Search(1);
        }
        else
            gstGetMess("Bạn không có quyền xem báo cáo này", "");
    }
    void PrintNPOI(System.Data.DataTable dt)
    { 
         var workbook = new HSSFWorkbook();

         if (dt.Rows.Count > 0)
         {
             string sheetname = "Bảng tổng hợp";

             if (dlKhachHang.SelectedIndex > 0)
                 sheetname = dlKhachHang.SelectedItem.Text;
             else if (dlLoaiKH.SelectedIndex > 0)
                 sheetname = "Bảng tổng hợp-" + dlLoaiKH.SelectedItem.Text;
             else sheetname = "Bảng tổng hợp";

             var sheet = workbook.CreateSheet(sheetname);

             #region CSS
             var headerLabelCellStyle = workbook.CreateCellStyle();
             headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
             headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
             headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
             headerLabelCellStyle.BorderRight = CellBorderType.THIN;
             headerLabelCellStyle.BorderTop = CellBorderType.THIN;

             var headerLabelFont = workbook.CreateFont();
             headerLabelFont.FontName = "Times New Roman";

             headerLabelFont.FontHeightInPoints = 11;
             headerLabelFont.Color = HSSFColor.BLACK.index;
             headerLabelCellStyle.SetFont(headerLabelFont);

             //--------------------------

             var hsRight = workbook.CreateCellStyle();
             hsRight.Alignment = HorizontalAlignment.RIGHT;
             hsRight.BorderBottom = CellBorderType.THIN;
             hsRight.BorderLeft = CellBorderType.THIN;
             hsRight.BorderRight = CellBorderType.THIN;
             hsRight.BorderTop = CellBorderType.THIN;

             hsRight.SetFont(headerLabelFont);
             //-------------------
             var hsCenter = workbook.CreateCellStyle();
             hsCenter.Alignment = HorizontalAlignment.CENTER;
             hsCenter.BorderBottom = CellBorderType.THIN;
             hsCenter.BorderLeft = CellBorderType.THIN;
             hsCenter.BorderRight = CellBorderType.THIN;
             hsCenter.BorderTop = CellBorderType.THIN;

             hsCenter.SetFont(headerLabelFont);
             //-----------------------

             var hs1 = workbook.CreateCellStyle();
             hs1.Alignment = HorizontalAlignment.LEFT;

             var hsb = workbook.CreateFont();
             hsb.Boldweight = (short)FontBoldWeight.BOLD;
             hsb.FontName = "Times New Roman";

             hsb.FontHeightInPoints = 11;
             hsb.Color = HSSFColor.BLACK.index;
             hs1.SetFont(hsb);

             //-------------
             var hs2 = workbook.CreateCellStyle();
             hs2.Alignment = HorizontalAlignment.CENTER;

             var hsb2 = workbook.CreateFont();
             hsb2.Boldweight = (short)FontBoldWeight.BOLD;
             hsb2.FontName = "Times New Roman";

             hsb2.FontHeightInPoints = 18;
             hsb2.Color = HSSFColor.BLACK.index;
             hs2.SetFont(hsb2);
             //------------------------------
             var hs3 = workbook.CreateCellStyle();
             hs3.Alignment = HorizontalAlignment.CENTER;
             hs3.BorderBottom = CellBorderType.THIN;
             var hsb3 = workbook.CreateFont();
             hsb3.Boldweight = (short)FontBoldWeight.BOLD;
             hsb3.FontName = "Times New Roman";

             hsb3.FontHeightInPoints = 14;
             hsb3.Color = HSSFColor.BLACK.index;
             hs3.SetFont(hsb3);

             //----------------------------
             var hs4 = workbook.CreateCellStyle();
             hs4.Alignment = HorizontalAlignment.CENTER;

             hs4.BorderBottom = CellBorderType.THIN;
             hs4.BorderLeft = CellBorderType.THIN;
             hs4.BorderRight = CellBorderType.THIN;
             hs4.BorderTop = CellBorderType.THIN;

             var hsb4 = workbook.CreateFont();
             hsb4.Boldweight = (short)FontBoldWeight.BOLD;
             hsb4.FontName = "Times New Roman";

             hsb4.FontHeightInPoints = 11;
             hsb4.Color = HSSFColor.BLACK.index;
             hs4.SetFont(hsb4);

             #endregion

             //Start header-----------------------------------------------------------------------
             #region Header
             //tao hnag dau tien
             var rowIndex = 0;

             //cong ty
             var row = sheet.CreateRow(rowIndex);
             Cell r1c1 = row.CreateCell(0);
             r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
             r1c1.CellStyle = hs1;
             r1c1.Row.Height = 400;

             CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
             sheet.AddMergedRegion(cra);

             //chi nhanh
             rowIndex++;
             row = sheet.CreateRow(rowIndex);
             r1c1 = row.CreateCell(0);
             r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
             r1c1.CellStyle = hs1;
             r1c1.Row.Height = 400;

             cra = new CellRangeAddress(1, 1, 0, 4);
             sheet.AddMergedRegion(cra);

             //tieu de bao cao
             string ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
             string congtrinh = dlCongTrinh.SelectedIndex > 0 ? " - " + dlCongTrinh.SelectedItem.Text : "";
             rowIndex++;
             row = sheet.CreateRow(rowIndex);
             r1c1 = row.CreateCell(0);
             r1c1.SetCellValue("BÁO CÁO TỔNG HỢP DOANH THU " + ngaythang);
             r1c1.CellStyle = hs2;
             r1c1.Row.Height = 500;

             cra = new CellRangeAddress(2, 2, 0, 12);
             sheet.AddMergedRegion(cra);

             //khachhang
             rowIndex++;
             row = sheet.CreateRow(rowIndex);
             r1c1 = row.CreateCell(0);
             r1c1.SetCellValue(dlKhachHang.SelectedIndex > 0 ? "Khách hàng: "+dlKhachHang.SelectedItem.Text+congtrinh : dlLoaiKH.SelectedIndex > 0 ? "Loại khách hàng: "+dlLoaiKH.SelectedItem.Text : "");
             r1c1.CellStyle = hs3;
             r1c1.Row.Height = 500;

             cra = new CellRangeAddress(3, 3, 0, 12);
             sheet.AddMergedRegion(cra);

             //freeze panes
             sheet.CreateFreezePane(0, 6);
             #endregion

             int sodong = 0;
             #region Header1-2
             
             //header2
             rowIndex++;
             rowIndex++;
             row = sheet.CreateRow(rowIndex);
             string[] header = { "Ngày xuất", "Số phiếu", "Biển số", "Lái xe", "Giờ xuất", "Khách hàng", "Độ sụt", "Mác TX", "KL TX", "Mác HĐ", "KL bán", "Loại đá", "Phụ gia", "Giá VL có VAT","Giá VL không VAT", "TTVL có VAT","TTVL không VAT", "Thành tiền VLTH", "Giá HĐ có VAT","Giá HĐ không VAT", "TT bê tông có VAT","TT bê tông không VAT", "Tổng chênh", "Chênh NVKD", "Chênh công ty", "TT chênh", "TT NVKD", "TT công ty", "Công trộn", "TT công trộn", "Cước VC", "Thành tiền CVC", "Doanh thu giá VL", "Doanh thu VLTH", "Tiền hỗ trợ cước", "Loại ca bơm", "Tiền ca bơm", "Thành tiền hàng", "NVKD", "Hình thức HĐ","VAT" };

             var cell = row.CreateCell(0);
             cell.SetCellValue("STT");
             cell.CellStyle = hs4;
             cell.Row.Height = 400;


             //tao tieu de cot
             for (int j = 0; j < header.Length; j++)
             {
                 cell = row.CreateCell(j+1);
                 cell.SetCellValue(header[j].ToString());
                 cell.CellStyle = hs4;
                 cell.Row.Height = 500;
             }
             #endregion
             //End header-----------------------------------------------------------------------
             //ghi du lieu tung dong
             
             for (int i = 0; i < dt.Rows.Count; i++)
             {
                 rowIndex++;
                 row = sheet.CreateRow(rowIndex);
                 cell = row.CreateCell(0);
                 cell.SetCellValue((i+1).ToString());
                 cell.CellStyle = hsCenter;

                 for (int j = 1; j <= dt.Columns.Count - 5; j++)
                 {
                     cell = row.CreateCell(j);
                     
                     if (j==9 || j == 11 || (j >= 14 && j <= 35) || (j >= 37 && j <= 38))
                     {
                         if (!dt.Rows[i][j-1].ToString().Trim().Equals(""))
                         {
                             cell.SetCellType(CellType.NUMERIC);
                             cell.SetCellValue(double.Parse(dt.Rows[i][j-1].ToString()));
                         }
                         else
                             cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                         cell.CellStyle = hsRight;
                     }
                     else
                     {
                         cell.SetCellValue(dt.Rows[i][j-1].ToString());

                         if (j <= 3 || j==5 || (j >=7 && j <= 12) || j==41)
                             cell.CellStyle = hsCenter;
                         else
                             cell.CellStyle = headerLabelCellStyle;
                     }
                     cell.Row.Height = 500;
                 }
                 if (i > 0)
                 {
                     if (dt.Rows[i][0].ToString().Equals(dt.Rows[i - 1][0].ToString()) && dt.Rows[i][5].ToString().Equals(dt.Rows[i - 1][5].ToString()) && dt.Rows[i][35].ToString().Equals(dt.Rows[i - 1][35].ToString()) && dt.Rows[i][35].ToString().ToLower().Contains("ca"))
                     {
                         sodong++;
                         cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 37, 37);
                         sheet.AddMergedRegion(cra);
                     }
                     else
                         sodong = 0;
                 }
             }

             #region Footer

             rowIndex++;
             row = sheet.CreateRow(rowIndex);

             cell = row.CreateCell(6);
             cell.SetCellValue("Tổng cộng");
             cell.Row.Height = 500;
             cell.CellStyle = hs4;

             string[] footer = { "J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM" };
             for (int k = 9; k <= 38; k++)
             {
                 
                 if (k == 37)
                 {
                     if (GV.Rows.Count > 0)
                     {
                         cell = row.CreateCell(k);
                         cell.SetCellValue(GV.FooterRow.Cells[38].Text);
                         cell.Row.Height = 500;
                         cell.CellStyle = hs4;
                     }
                 }
                 if (k != 10 && k != 12 && k != 13 && k!=36 && k != 37)
                 {
                     cell = row.CreateCell(k);
                     if (k == 14 || k==15 || k == 19 || k==20 || k == 29 || k == 31)
                         cell.CellFormula = "AVERAGE(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")";
                     else
                     {
                         if (k == 38)
                             cell.CellFormula = "Sum(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")+AL" + (rowIndex+1).ToString();
                         else
                             cell.CellFormula = "Sum(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")";
                     }
                     cell.Row.Height = 500;
                     cell.CellStyle = hs4;
                 }

             }
             //fix lai border
             for (int k = 0; k <= 41; k++)
             {
                 if ((k != 6 && k <= 8) || k == 10 || k == 12 || k == 13 || k==36 || k == 39 || k == 40 || k==41)
                 {
                     r1c1 = row.CreateCell(k);
                     r1c1.CellStyle = hs4;
                     r1c1.Row.Height = 400;
                 }
             }
             for (int ik = 0; ik <=41; ik++)
             {
                 sheet.AutoSizeColumn(ik);
             }
             #endregion

             //end sheet tong hop--------------------------
             
             
             //in tung khach hang vao tung sheet
             double cabom = 0;
             sodong = 0;
             if (dlKhachHang.SelectedIndex.Equals(0))
             {
                 
                 int STT = 0;
                 //ghi sheet con------------------------------------------
                 for (int i = 0; i < dt.Rows.Count; i++)
                 {
                     STT++;
                     #region Start
                     if (i > 0)
                     {
                         ncc = dt.Rows[i]["TenKhachHang"].ToString();
                         
                         if (!ncc.Equals(dt.Rows[i - 1]["TenKhachHang"].ToString()))
                         {
                             cabom = 0;
                             STT = 0;
                             #region Footer
                             //-----------Footer
                             rowIndex++;
                             row = sheet.CreateRow(rowIndex);
                             string kiuu = dt.Rows[i - 1]["f_KhachHang"].ToString() + " " + dt.Rows[i - 1]["f_TuNgay"].ToString() + " " + dt.Rows[i - 1]["f_LoaiKH"].ToString();
                             cell = row.CreateCell(6);
                             cell.SetCellValue("Tổng cộng");
                             cell.Row.Height = 500;
                             cell.CellStyle = hs4;
                             
                             cabom = ReturnCaBom(dt.Rows[i - 1]["f_KhachHang"].ToString(), dt.Rows[i - 1]["f_TuNgay"].ToString(), dt.Rows[i - 1]["f_DenNgay"].ToString());

                             for (int k = 9; k <= 38; k++)
                             {
                                 if (k != 10 && k != 12 && k != 13 && k != 36 && k != 37)
                                 {
                                     cell = row.CreateCell(k);
                                     if (k == 14 || k == 15 || k == 19 || k == 20 || k == 29 || k == 31)
                                         cell.CellFormula = "AVERAGE(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")";
                                     else
                                     {
                                         if (k == 38)
                                             cell.CellFormula = "Sum(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")+AL" + (rowIndex + 1).ToString();
                                         else
                                             cell.CellFormula = "Sum(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")";
                                     }

                                     cell.Row.Height = 500;
                                     cell.CellStyle = hs4;
                                 }
                                 if (k == 37)
                                 {
                                     cell = row.CreateCell(k);
                                     cell.SetCellValue(cabom.ToString());
                                     cell.Row.Height = 500;
                                     cell.CellStyle = hs4;
                                 }
                             }
                             //fix lai border
                             for (int k = 0; k <= 41; k++)
                             {
                                 if ((k != 6 && k <= 8) || k == 10 || k == 12 || k == 13 || k == 36 || k == 39 || k == 40 || k==41)
                                 {
                                     r1c1 = row.CreateCell(k);
                                     r1c1.CellStyle = hs4;
                                     r1c1.Row.Height = 400;
                                 }
                             }
                             for (int ik = 0; ik <= 40; ik++)
                             {
                                 sheet.AutoSizeColumn(ik);
                             }
                             //---End Footer
                             #endregion
                             sheet = workbook.CreateSheet(cl.EscapeSheetName(ncc));
                             rowIndex = 0;

                             //Start header-----------------------------------------------------------------------
                             #region Header
                             //tao hnag dau tien

                             //cong ty
                             row = sheet.CreateRow(rowIndex);
                             r1c1 = row.CreateCell(0);
                             r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                             r1c1.CellStyle = hs1;
                             r1c1.Row.Height = 400;

                             cra = new CellRangeAddress(0, 0, 0, 4);
                             sheet.AddMergedRegion(cra);

                             //chi nhanh
                             rowIndex++;
                             row = sheet.CreateRow(rowIndex);
                             r1c1 = row.CreateCell(0);
                             r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                             r1c1.CellStyle = hs1;
                             r1c1.Row.Height = 400;

                             cra = new CellRangeAddress(1, 1, 0, 4);
                             sheet.AddMergedRegion(cra);

                             //tieu de bao cao
                             rowIndex++;
                             row = sheet.CreateRow(rowIndex);
                             r1c1 = row.CreateCell(0);
                             r1c1.SetCellValue("BÁO CÁO TỔNG HỢP DOANH THU " + ngaythang);
                             r1c1.CellStyle = hs2;
                             r1c1.Row.Height = 500;

                             cra = new CellRangeAddress(2, 2, 0, 12);
                             sheet.AddMergedRegion(cra);

                             //khachhang
                             rowIndex++;
                             row = sheet.CreateRow(rowIndex);
                             r1c1 = row.CreateCell(0);
                             r1c1.SetCellValue("Khách hàng: "+ncc);
                             r1c1.CellStyle = hs3;
                             r1c1.Row.Height = 500;

                             cra = new CellRangeAddress(3, 3, 0, 12);
                             sheet.AddMergedRegion(cra);

                             //freeze panes
                             sheet.CreateFreezePane(0, 6);
                             #endregion
                             
                             #region Header1-2

                             //header2
                             
                             rowIndex=rowIndex+2;
                             row = sheet.CreateRow(rowIndex);

                             cell = row.CreateCell(0);
                             cell.SetCellValue("STT");
                             cell.CellStyle = hs4;
                             cell.Row.Height = 400;


                             //tao tieu de cot
                             for (int j = 0; j < header.Length; j++)
                             {
                                 cell = row.CreateCell(j + 1);
                                 cell.SetCellValue(header[j].ToString());
                                 cell.CellStyle = hs4;
                                 cell.Row.Height = 500;
                             }
                             #endregion
                             //End header-----------------------------------------------------------------------
                            
                             STT++;
                     
                         }
                     }
                     else
                     {
                         ncc = "";
                         if (!ncc.Equals(dt.Rows[i]["TenKhachHang"].ToString()))
                         {
                             ncc = dt.Rows[i]["TenKhachHang"].ToString();
                             sheet = workbook.CreateSheet(cl.EscapeSheetName(ncc));
                             rowIndex = 0;

                             //Start header-----------------------------------------------------------------------
                             #region Header
                             row = sheet.CreateRow(rowIndex);
                             r1c1 = row.CreateCell(0);
                             r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                             r1c1.CellStyle = hs1;
                             r1c1.Row.Height = 400;

                             cra = new CellRangeAddress(0, 0, 0, 4);
                             sheet.AddMergedRegion(cra);

                             //chi nhanh
                             rowIndex++;
                             row = sheet.CreateRow(rowIndex);
                             r1c1 = row.CreateCell(0);
                             r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                             r1c1.CellStyle = hs1;
                             r1c1.Row.Height = 400;

                             cra = new CellRangeAddress(1, 1, 0, 4);
                             sheet.AddMergedRegion(cra);

                             //tieu de bao cao
                             rowIndex++;
                             row = sheet.CreateRow(rowIndex);
                             r1c1 = row.CreateCell(0);
                             r1c1.SetCellValue("TỔNG HỢP SẢN LƯỢNG BÁN HÀNG " + ngaythang);
                             r1c1.CellStyle = hs2;
                             r1c1.Row.Height = 500;

                             cra = new CellRangeAddress(2, 2, 0, 12);
                             sheet.AddMergedRegion(cra);

                             //nha cung cap
                             rowIndex++;
                             row = sheet.CreateRow(rowIndex);
                             r1c1 = row.CreateCell(0);
                             r1c1.SetCellValue("Khách hàng: " + ncc);
                             r1c1.CellStyle = hs3;
                             r1c1.Row.Height = 500;

                             cra = new CellRangeAddress(3, 3, 0, 12);
                             sheet.AddMergedRegion(cra);

                             //freeze panes
                             sheet.CreateFreezePane(0, 6);
                             //End header-----------------------------------------------------------------------

                             //header2
                             rowIndex = rowIndex + 2;
                             row = sheet.CreateRow(rowIndex);

                             cell = row.CreateCell(0);
                             cell.SetCellValue("STT");
                             cell.CellStyle = hs4;
                             cell.Row.Height = 400;


                             //tao tieu de cot
                             for (int j = 1; j <= header.Length; j++)
                             {
                                 cell = row.CreateCell(j);
                                 cell.SetCellValue(header[j - 1].ToString());
                                 cell.CellStyle = hs4;
                                 cell.Row.Height = 500;
                             }
                             #endregion
                         }
                         for (int ik = 0; ik <= 36; ik++)
                         {
                             sheet.AutoSizeColumn(ik);
                         }
                     }
                     #endregion
                     rowIndex++;
                     row = sheet.CreateRow(rowIndex);

                     cell = row.CreateCell(0);
                     cell.SetCellValue((STT).ToString());
                     cell.CellStyle = hsCenter;


                     for (int j = 1; j <= dt.Columns.Count - 5; j++)
                     {
                         cell = row.CreateCell(j);

                         if (j == 9 || j == 11 || (j >= 14 && j <= 35) || (j >= 37 && j <= 38))
                         {
                             if (!dt.Rows[i][j-1].ToString().Trim().Equals(""))
                             {
                                 cell.SetCellType(CellType.NUMERIC);
                                 cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                             }
                             else
                                 cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                             cell.CellStyle = hsRight;
                         }
                         else
                         {
                             cell.SetCellValue(dt.Rows[i][j - 1].ToString());

                             if (j <= 3 || j == 5 || (j >= 7 && j <= 12) || j==41)
                                 cell.CellStyle = hsCenter;
                             else
                                 cell.CellStyle = headerLabelCellStyle;
                         }
                         cell.Row.Height = 500;
                     }
                     if (i > 0)
                     {
                         if (dt.Rows[i][0].ToString().Equals(dt.Rows[i - 1][0].ToString()) && dt.Rows[i][5].ToString().Equals(dt.Rows[i - 1][5].ToString()) && dt.Rows[i][35].ToString().Equals(dt.Rows[i - 1][35].ToString()) && dt.Rows[i][35].ToString().ToLower().Contains("ca"))
                         {
                             sodong++;
                             cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 37, 37);
                             sheet.AddMergedRegion(cra);
                         }
                         else
                             sodong = 0;
                     }
                     #region Footer
                     if (i == dt.Rows.Count - 1)
                     {
                         rowIndex++;
                         row = sheet.CreateRow(rowIndex);

                         cabom = 0;
                         cell = row.CreateCell(6);
                         cell.SetCellValue("Tổng cộng");
                         cell.Row.Height = 500;
                         cell.CellStyle = hs4;
                         
                         cabom = ReturnCaBom(dt.Rows[i - 1]["f_KhachHang"].ToString(), dt.Rows[i - 1]["f_TuNgay"].ToString(), dt.Rows[i - 1]["f_DenNgay"].ToString());
                         
                         for (int k = 9; k <= 38; k++)
                         {
                             if (k != 10 && k != 12 && k != 13 && k != 36 && k != 37)
                             {
                                 cell = row.CreateCell(k);
                                 if (k == 14 || k == 15 || k == 19 || k == 20 || k == 29 || k == 31)
                                     cell.CellFormula = "AVERAGE(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")";
                                 else
                                 {
                                     if (k == 38)
                                         cell.CellFormula = "Sum(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")+AL" + (rowIndex + 1).ToString();
                                     else
                                         cell.CellFormula = "Sum(" + footer[k - 9] + "7:" + footer[k - 9] + rowIndex.ToString() + ")";
                                 }

                                 cell.Row.Height = 500;
                                 cell.CellStyle = hs4;
                             }
                             if (k == 37)
                             {
                                 cell = row.CreateCell(k);
                                 cell.SetCellValue(cabom.ToString());
                                 cell.Row.Height = 500;
                                 cell.CellStyle = hs4;
                             }
                         }
                         //fix lai border
                         for (int k = 0; k <= 41; k++)
                         {
                             if ((k != 6 && k <= 8) || k == 10 || k == 12 || k == 13 || k == 36 || k == 39 || k == 40 || k==41)
                             {
                                 r1c1 = row.CreateCell(k);
                                 r1c1.CellStyle = hs4;
                                 r1c1.Row.Height = 400;
                             }
                         }
                         for (int ik = 0; ik <= 41; ik++)
                         {
                             sheet.AutoSizeColumn(ik);
                         }
                     }
                     #endregion
                 }
             }

             //
             //print thanh tien hang
             int ind = 3;
             if (dlKhachHang.SelectedIndex > 0 && dlLoaiKH.SelectedIndex > 0)
                 ind = 1;
             else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex.Equals(0))
                 ind = 2;
             else if (dlLoaiKH.SelectedIndex.Equals(0) && dlKhachHang.SelectedIndex.Equals(0))
                 ind = 3;


             getDate();
             p = new SqlParameter[5];
             p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
             p[0].SqlDbType = SqlDbType.SmallDateTime;
             p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
             p[1].SqlDbType = SqlDbType.SmallDateTime;
             p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
             p[2].SqlDbType = SqlDbType.UniqueIdentifier;
             p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
             p[3].SqlDbType = SqlDbType.UniqueIdentifier;
             p[4] = new SqlParameter("@Index", ind);
             p[4].SqlDbType = SqlDbType.Int;

             dt = ex.GetData("sp_BCDT_TH_TongHop_ThanhTienHang_Print", p);
             if (dt.Rows.Count > 0)
             {
                 sheet = workbook.CreateSheet("Thành tiền hàng");
                 #region Header
                 //tao hnag dau tien
                 rowIndex = 0;

                 //cong ty
                 row = sheet.CreateRow(rowIndex);
                 r1c1 = row.CreateCell(0);
                 r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                 r1c1.CellStyle = hs1;
                 r1c1.Row.Height = 400;

                 cra = new CellRangeAddress(0, 0, 0, 4);
                 sheet.AddMergedRegion(cra);

                 //chi nhanh
                 rowIndex++;
                 row = sheet.CreateRow(rowIndex);
                 r1c1 = row.CreateCell(0);
                 r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                 r1c1.CellStyle = hs1;
                 r1c1.Row.Height = 400;

                 cra = new CellRangeAddress(1, 1, 0, 4);
                 sheet.AddMergedRegion(cra);

                 //tieu de bao cao
                 rowIndex++;
                 row = sheet.CreateRow(rowIndex);
                 r1c1 = row.CreateCell(0);
                 r1c1.SetCellValue("TỔNG HỢP THÀNH TIỀN HÀNG " + ngaythang);
                 r1c1.CellStyle = hs2;
                 r1c1.Row.Height = 500;

                 cra = new CellRangeAddress(2, 2, 0, 5);
                 sheet.AddMergedRegion(cra);

                 //khachhang
                 rowIndex++;
                 
                 //freeze panes
                 sheet.CreateFreezePane(0, 6);
                 #endregion

                 #region Header1-2

                 //header2
                 rowIndex++;
                 rowIndex++;
                 row = sheet.CreateRow(rowIndex);
                 string[] header5 = { "Khách hàng","Tiền bê tông","Tiền bơm","Tiền hỗ trợ cước","Thành tiền hàng" };

                 cell = row.CreateCell(0);
                 cell.SetCellValue("STT");
                 cell.CellStyle = hs4;
                 cell.Row.Height = 400;


                 //tao tieu de cot
                 for (int j = 0; j < header5.Length; j++)
                 {
                     cell = row.CreateCell(j + 1);
                     cell.SetCellValue(header5[j].ToString());
                     cell.CellStyle = hs4;
                     cell.Row.Height = 500;
                 }
                 #endregion

                 for (int i = 0; i < dt.Rows.Count; i++)
                 {
                     rowIndex++;
                     row = sheet.CreateRow(rowIndex);
                     cell = row.CreateCell(0);
                     cell.SetCellValue((i + 1).ToString());
                     cell.CellStyle = hsCenter;

                     for (int j = 1; j < dt.Columns.Count; j++)
                     {
                         cell = row.CreateCell(j);

                         if (j >1)
                         {
                             if (!dt.Rows[i][j - 1].ToString().Trim().Equals(""))
                             {
                                 cell.SetCellType(CellType.NUMERIC);
                                 cell.SetCellValue(double.Parse(dt.Rows[i][j - 1].ToString()));
                             }
                             else
                                 cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                             cell.CellStyle = hsRight;
                         }
                         else
                         {
                             cell.SetCellValue(dt.Rows[i][j - 1].ToString());
                             cell.CellStyle = headerLabelCellStyle;   
                         }

                         cell.Row.Height = 500;
                     }
                     cell = row.CreateCell(5);
                     cell.CellFormula = "Sum(C" + (rowIndex+1).ToString() + ":E" + (rowIndex+1).ToString() + ")";
                     cell.Row.Height = 500;
                     cell.CellStyle = hsRight;
                 }

                 #region Footer

                 rowIndex++;
                 row = sheet.CreateRow(rowIndex);

                 cell = row.CreateCell(1);
                 cell.SetCellValue("Tổng cộng");
                 cell.Row.Height = 500;
                 cell.CellStyle = hs4;

                 string[] footer5 = { "C","D","E","F" };
                 for (int k = 2; k <= 5; k++)
                 {
                     cell = row.CreateCell(k);
                     cell.CellFormula = "Sum(" + footer5[k - 2] + "7:" + footer5[k - 2] + rowIndex.ToString() + ")";
                     cell.Row.Height = 500;
                     cell.CellStyle = hs4;
                 }
                 //fix lai border
                 r1c1 = row.CreateCell(0);
                 r1c1.CellStyle = hs4;
                 r1c1.Row.Height = 400;
                 for (int ik = 0; ik <= 5; ik++)
                 {
                     sheet.AutoSizeColumn(ik);
                 }
                 sheet.SetColumnWidth(5, 128 * 36);
                 #endregion
             }
             using (var exportData = new MemoryStream())
             {
                 workbook.Write(exportData);
                 string filename = dlKhachHang.SelectedIndex > 0 ? dlKhachHang.SelectedItem.Text : dlLoaiKH.SelectedIndex > 0 ? dlLoaiKH.SelectedItem.Text : "";
                 string saveAsFileName = "BCDT-TongHop-" + filename + " " + ngaythang + ".xls";

                 Response.ContentType = "application/vnd.ms-excel";
                 Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                 Response.Clear();
                 Response.BinaryWrite(exportData.GetBuffer());
                 Response.End();
             }
         }
    }
    private double ReturnCaBom(string KhachHang,string Tungay,string denngay)
    {
        double tiencabom = 0;
        p = new SqlParameter[3];
        p[0] = new SqlParameter("@KhachHang", ex.GetGuid(KhachHang));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@TuNgay", ex.GetNgayThang(Tungay));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@DenNgay", ex.GetNgayThang(denngay));
        p[2].SqlDbType = SqlDbType.SmallDateTime;

        dt = ex.GetData("sp_BCDT_GetTongTienCaBomByKH", p);
        if (dt.Rows.Count > 0)
        {
            tiencabom = double.Parse(dt.Rows[0]["TienCaBom"].ToString());
        }
        else
        {
            tiencabom = 0;
        }
        return tiencabom;
    }
    private void Search(int page)
    {
        int index = 3;
        if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex.Equals(0))
            index = 1;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0))
            index = 2;
        else if (dlLoaiKH.SelectedIndex.Equals(0) && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0))
            index = 3;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex>0)
            index = 4;

        getDate();
        p = new SqlParameter[8];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
        p[4] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
        p[4].SqlDbType = SqlDbType.UniqueIdentifier;
        p[5] = new SqlParameter("@Index", index);
        p[5].SqlDbType = SqlDbType.Int;
        p[6] = new SqlParameter("@PageNumber", page);
        p[6].SqlDbType = SqlDbType.Int;
        p[7] = new SqlParameter("@RowspPage", PageNumber);
        p[7].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BCDT_TH_TongHop", p);
        GV.DataBind();

        p = new SqlParameter[6];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
        p[4] = new SqlParameter("@Index", index);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
        p[5].SqlDbType = SqlDbType.UniqueIdentifier;

        if (GV.Rows.Count > 0)
        {
            dt = ex.GetData("sp_BCDT_TH_TongHop_GetFooter", p);
            if (dt.Rows.Count > 0)
            {
                kltx = double.Parse(dt.Rows[0]["KLThucXuat"].ToString());
                klban = double.Parse(dt.Rows[0]["KLKhachHang"].ToString());
                ttvl = double.Parse(dt.Rows[0]["TTVL"].ToString());
                ttvlkhongvat = double.Parse(dt.Rows[0]["TTVLKhongVAT"].ToString());
                ttvlth = double.Parse(dt.Rows[0]["TTVLTH"].ToString());
                ttban = double.Parse(dt.Rows[0]["TTGiaHD"].ToString());
                ttbankhongvat = double.Parse(dt.Rows[0]["TTGiaHDKhongVAT"].ToString());
                tongchenh = double.Parse(dt.Rows[0]["ChenhKL"].ToString());
                chenhnvkd = double.Parse(dt.Rows[0]["KLNVKD"].ToString());
                chenhcongty = double.Parse(dt.Rows[0]["KLCongTy"].ToString());
                ttchenh = double.Parse(dt.Rows[0]["TTChenh"].ToString());
                ttcongty = double.Parse(dt.Rows[0]["TTCongTy"].ToString());
                ttnvkd = double.Parse(dt.Rows[0]["TTNVKD"].ToString());
                ttcongtron = double.Parse(dt.Rows[0]["TTCongTron"].ToString());
                ttcvc = double.Parse(dt.Rows[0]["TTCVC"].ToString());
                dthugiavl = double.Parse(dt.Rows[0]["DTGiaVL"].ToString());
                dthuvlth = double.Parse(dt.Rows[0]["DTTHVL"].ToString());
                hotrocuoc = double.Parse(dt.Rows[0]["TienHoTroCuoc"].ToString());

                //tienhang = ttban + hotrocuoc;// double.Parse(dt.Rows[0]["ThanhTienHang"].ToString());

                GV.FooterRow.Cells[7].Text = "Tổng cộng";
                GV.FooterRow.Cells[7].HorizontalAlign = HorizontalAlign.Center;
                GV.FooterRow.Cells[10].Text = ex.ConvertDecimal(kltx);
                GV.FooterRow.Cells[12].Text = ex.ConvertDecimal(klban);

                GV.FooterRow.Cells[17].Text = ex.ConvertDecimal(ttvl);
                GV.FooterRow.Cells[18].Text = ex.ConvertDecimal(ttvlkhongvat);
                GV.FooterRow.Cells[19].Text = ex.ConvertDecimal(ttvlth);

                GV.FooterRow.Cells[22].Text = ex.ConvertDecimal(ttban);
                GV.FooterRow.Cells[23].Text = ex.ConvertDecimal(ttbankhongvat);

                GV.FooterRow.Cells[24].Text = ex.ConvertDecimal(tongchenh);
                GV.FooterRow.Cells[25].Text = ex.ConvertDecimal(chenhnvkd);
                GV.FooterRow.Cells[26].Text = ex.ConvertDecimal(chenhcongty);

                GV.FooterRow.Cells[27].Text = ex.ConvertDecimal(ttchenh);
                GV.FooterRow.Cells[28].Text = ex.ConvertDecimal(ttnvkd);
                GV.FooterRow.Cells[29].Text = ex.ConvertDecimal(ttcongty);


                GV.FooterRow.Cells[31].Text = ex.ConvertDecimal(ttcongtron);
                GV.FooterRow.Cells[33].Text = ex.ConvertDecimal(ttcvc);
                GV.FooterRow.Cells[34].Text = ex.ConvertDecimal(dthugiavl);

                GV.FooterRow.Cells[35].Text = ex.ConvertDecimal(dthuvlth);
                GV.FooterRow.Cells[36].Text = ex.ConvertDecimal(hotrocuoc);


                try
                {
                    p = new SqlParameter[4];
                    p[0] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[1] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                    p[2].SqlDbType = SqlDbType.SmallDateTime;
                    p[3] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
                    p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                    dt = ex.GetData("sp_BCDT_GetTongTienCaBom", p);
                    if (dt.Rows.Count > 0)
                    {
                        tiencabom = double.Parse(dt.Rows[0]["TienCaBom"].ToString());
                        GV.FooterRow.Cells[38].Text = ex.ConvertDecimal(tiencabom);
                    }
                    else
                    {
                        tiencabom = 0;
                        GV.FooterRow.Cells[38].Text = "0";
                    }
                }
                catch
                {
                    tiencabom = 0;
                }
                GV.FooterRow.Cells[39].Text = ex.ConvertDecimal(ttban + ttbankhongvat + hotrocuoc + tiencabom);
            }
        }
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {
            try
            {
                if (f_InBaoCao.Equals(1))
                {
                    int ind = 3;
                    if (dlKhachHang.SelectedIndex > 0 && dlLoaiKH.SelectedIndex > 0)
                        ind = 1;
                    else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex.Equals(0))
                        ind = 2;
                    else if (dlLoaiKH.SelectedIndex.Equals(0) && dlKhachHang.SelectedIndex.Equals(0))
                        ind = 3;


                    getDate();
                    p = new SqlParameter[5];
                    p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
                    p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
                    p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[4] = new SqlParameter("@Index", ind);
                    p[4].SqlDbType = SqlDbType.Int;

                    dtPrint = ex.GetData("sp_BCDT_TH_TongHop_Print", p);

                    PrintNPOI(dtPrint);

                }
                else gstGetMess("Bạn không có quyền in báo cáo này", "");
            }
            catch (Exception ax)
            {
                gstGetMess(ax.Message, "");
            }
        }
        else
            gstGetMess("Bạn không có quyền in báo cáo này", "");
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
        }
        dlLoaiKH.SelectedIndex = 0;
        LoadKhachHang(dlKhachHang);
        getDate();

    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }
    protected void dlLoaiKH_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiKH.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlKhachHang.Items.Clear();
            dlKhachHang.DataSource = ex.GetData("sp_BCXH_LoadKhachHangByLoaiKH", p);
            dlKhachHang.DataBind();
            dlKhachHang.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
            dlKhachHang.SelectedIndex = 0;
        }
        else
        {
            LoadKhachHang(dlKhachHang);
        }
        LoadCongTrinh(dlCongTrinh);
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        Search(CurrentPage);

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            Search(CurrentPage);
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        Search(CurrentPage);

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;
    }
    protected void GV_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        //if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)
        //{
        //    e.Row.Attributes.Add("onmouseover", "this.originalstyle=this.style.backgroundColor;this.style.backgroundColor='#EEFFAA'");
        //    e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=this.originalstyle;");
        //}
        for (int rowIndex = GV.Rows.Count - 2; rowIndex >= 0; rowIndex--)
        {
            GridViewRow gvRow = GV.Rows[rowIndex];
            GridViewRow gvPreviousRow = GV.Rows[rowIndex + 1];

            if (gvRow.Cells[37].Text.Trim().ToLower().Contains("ca") && gvRow.Cells[1].Text.Equals(gvPreviousRow.Cells[1].Text) && gvRow.Cells[7].Text.Equals(gvPreviousRow.Cells[7].Text) && gvRow.Cells[37].Text.Equals(gvPreviousRow.Cells[37].Text) && gvRow.Cells[38].Text.Equals(gvPreviousRow.Cells[38].Text))
            {

                if (gvPreviousRow.Cells[1].RowSpan < 2)
                {
                    gvRow.Cells[1].RowSpan = 2;
                    gvRow.Cells[7].RowSpan = 2;
                    gvRow.Cells[37].RowSpan = 2;
                    gvRow.Cells[38].RowSpan = 2;
                }
                else
                {
                    gvRow.Cells[1].RowSpan = gvPreviousRow.Cells[1].RowSpan + 1;
                    gvRow.Cells[7].RowSpan = gvPreviousRow.Cells[7].RowSpan + 1;
                    gvRow.Cells[37].RowSpan = gvPreviousRow.Cells[37].RowSpan + 1;
                    gvRow.Cells[38].RowSpan = gvPreviousRow.Cells[38].RowSpan + 1;
                }
                gvPreviousRow.Cells[1].Visible = false;
                gvPreviousRow.Cells[7].Visible = false;
                gvPreviousRow.Cells[37].Visible = false;
                gvPreviousRow.Cells[38].Visible = false;
            }
        }
    }
    protected void dlKhachHang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlKhachHang.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlCongTrinh.Items.Clear();
            dlCongTrinh.DataSource = ex.GetData("sp_BCXH_LoadCongTrinhByKhachHang", p);
            dlCongTrinh.DataBind();
            dlCongTrinh.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
            dlCongTrinh.SelectedIndex = 0;
        }
        else
        {
            LoadCongTrinh(dlCongTrinh);
        }
    }
}